module.exports = app =>{
    const express = require('express')
    const router = express.Router()

    const assert = require('http-assert')
    const jwt = require('jsonwebtoken')
    const _ = require('lodash')
    const md5 = require('md5')
    const multer = require('multer')
    const dayjs = require('dayjs')

    const {query} = require('../../plugins/db')
    const permissions = require('../../constants/permissions')
    


    router.get('/user',async (req, res)=>{
        // console.log(req.query.id);
        let user = await query('select * from user where user_id = ?',[req.query.id])
        user = user[0]
        // console.log(user);
        assert(user, 400, '用户ID不存在')
        let permission =  permissions.filter(item=>item.grade === user.grade)
        assert(!_.isEmpty(permission), 402, '普通用户无权登录')
        // console.log(permission);
        _.set(user,'permissions',permission[0].permissions)
        res.send(user)
    })
    
    router.put('/update_pwd', async (req, res)=>{
        const {user_id, user_pwd} = req.body
        console.log(user_id, user_pwd)
        let sql = `update user set  user_pwd = '${user_pwd}' where user_id = ${user_id}`
        let row = await query(sql)
        assert(!_.isEmpty(row), 402, '密码修改失败' )
        let user = await query('select * from user where user_id = ?',[user_id])
        res.send(user[0])
    })

    router.put('/update_user', async(req, res)=>{
        const {user_id, user_desc, user_name, user_email} = req.body
        console.log(user_id, user_desc, user_name, user_email);
        let sql = `UPDATE user SET user_email = ?
                    ,user_desc = ?
                    ,user_name = ? 
                    WHERE user_id = ?`;
        let row = await query(sql, [user_email, user_desc, user_name, user_id])
        assert(!_.isEmpty(row), 402, '修改失败' )
        let user = await query('select * from user where user_id = ?',[user_id])
        res.send(user[0])
    })

    // 管理所有用户
    router.get('/all_users', async(req, res)=>{
        const {currPage, pageSize} = req.query;
        let total =await query(`SELECT count(*) as total FROM user WHERE grade = 'user'`)
        total = total[0].total
        assert(total,402, '系统还没有用户！！！')
        let sql = `SELECT * FROM user WHERE grade = 'user' limit ${(currPage-1)*pageSize},${pageSize}`
        let users = await query(sql)
        // 处理时间格式
        users = users.map(item=>{
            item.ctime = dayjs(item.ctime).format('YYYY-MM-DD HH:mm:ss')
            return item
        })
        let result = {users, total, pageSize, currPage}
        res.send(result)
    })

    router.get('/search_user', async(req, res)=>{
        const {keyword, currPage, pageSize} = req.query;
        let total = await query(`SELECT count(*) as total FROM user WHERE grade = 'user' AND (user_name LIKE '%${keyword}%' OR user_account LIKE '%${keyword}%')`);
        total = total[0].total

        let sql = `SELECT * FROM user WHERE grade = 'user' AND
            (user_name LIKE '%${keyword}%' OR user_account LIKE '%${keyword}%') 
            limit ${(currPage-1)*pageSize},${pageSize}`;
        let users = await query(sql)
        let result = {users, total, currPage, pageSize}
        res.send(result)
    })

    // 新增用户
    router.post('/insert_user', async(req, res)=>{
        let {user_name, user_pwd, user_desc, user_account,
                grade, user_email, user_cover} = req.body;
        assert(user_account,402, '账号不能为空')
        // 判断用户是否已经存在
        let verify = await query(`SELECT * FROM user WHERE user_account = '${user_account}'`)
        assert(_.isEmpty(verify),402 , '用户已经存在')
        user_pwd = md5(user_pwd)
        let ctime = dayjs(Date.now()).format('YYYY-MM-DD HH:mm:ss')
        // 添加新用户
        let sql = `INSERT INTO user(user_name, user_pwd, user_desc, user_account,
            grade, user_email, user_cover,ctime) values('${user_name}','${user_pwd}',
            '${user_desc}','${user_account}','${grade}','${user_email}','${user_cover}','${ctime}')`
        await query(sql)
        res.send({type:'success',message:'用户添加成功'})
    })

    // 删除用户
    router.delete('/delete_user', async(req, res)=>{
        const {user_id } = req.query;
        console.log(req.query);
        let sql = `DELETE FROM user WHERE user_id = ${user_id}`;
        console.log(sql);
        await query(sql)

        res.send({type:'success',message:'删除成功'})
    })
    // 管理员更新用户
    router.put('/admin_update_user', async(req, res)=>{
        const {user_id, user_name, user_account, user_email, grade, user_desc, user_cover} = req.body;
        let sql = `UPDATE user SET user_name='${user_name}', user_account='${user_account}', 
                    user_email='${user_email}', grade='${grade}', user_desc='${user_desc}',
                     user_cover='${user_cover}' WHERE user_id = ${user_id}`
        await query(sql)
        res.send({type:'success',message:'更新成功'})
    })

    /**
     * 作者操作
     */
     // 管理所有作者
     router.get('/all_authors', async(req, res)=>{
        const {currPage, pageSize} = req.query;
        let total =await query(`SELECT count(*) as total FROM user WHERE grade = 'author'`)
        total = total[0].total
        assert(total,402, '系统还没有作者！！！')
        let sql = `SELECT * FROM user WHERE grade = 'author' limit ${(currPage-1)*pageSize},${pageSize}`
        let authors = await query(sql)
        // 处理时间格式
        authors = authors.map(item=>{
            item.ctime = dayjs(item.ctime).format('YYYY-MM-DD HH:mm:ss')
            return item
        })
        let result = {authors, total, pageSize, currPage}
        res.send(result)
    })
  // 搜索作家
    router.get('/search_author', async(req, res)=>{
        const {keyword, currPage, pageSize} = req.query;
        let total = await query(`SELECT count(*) as total FROM user WHERE grade = 'author' AND (user_name LIKE '%${keyword}%' OR user_account LIKE '%${keyword}%')`);
        total = total[0].total

        let sql = `SELECT * FROM user WHERE grade = 'author' AND
            (user_name LIKE '%${keyword}%' OR user_account LIKE '%${keyword}%') 
            limit ${(currPage-1)*pageSize},${pageSize}`;
        let authors = await query(sql)
        let result = {authors, total, currPage, pageSize}
        res.send(result)
    })

    // 管理员更新作家
    router.put('/admin_update_author', async(req, res)=>{
        const {user_id, user_name, user_account, user_email, grade, user_desc, user_cover, pen_name} = req.body;
        let sql = `UPDATE user SET user_name='${user_name}', user_account='${user_account}', 
                    user_email='${user_email}', grade='${grade}', user_desc='${user_desc}',
                    user_cover='${user_cover}', pen_name = '${pen_name}' WHERE user_id = ${user_id}`
        await query(sql)
        res.send({type:'success',message:'更新成功'})
    })
     // 新增作者
     router.post('/insert_author', async(req, res)=>{
        let {user_name,pen_name,  user_pwd, user_desc, user_account,
                grade, user_email, user_cover} = req.body;
        assert(user_account,402, '账号不能为空')
        // 判断用户是否已经存在
        let verify = await query(`SELECT * FROM user WHERE user_account = '${user_account}'`)
        assert(_.isEmpty(verify),402 , '用户已经存在')
        user_pwd = md5(user_pwd)
        let ctime = dayjs(Date.now()).format('YYYY-MM-DD HH:mm:ss')
        // 添加新用户
        let sql = `INSERT INTO user(user_name, user_pwd, user_desc, user_account,
            grade, user_email, user_cover,ctime, pen_name) values('${user_name}','${user_pwd}',
            '${user_desc}','${user_account}','${grade}','${user_email}','${user_cover}',
            '${ctime}','${pen_name}')`
        await query(sql)
        res.send({type:'success',message:'作者添加成功'})
    })

    /**
     * 小说阅读
     */
    router.get('/novels', async (req, res)=>{
        const {user_id, currPage, pageSize} = req.query
        // 查询用户的权限，管理员查看所有，作者只能看自己作品
        let authority_sql = `SELECT grade FROM user WHERE user_id = ${user_id}`
        let authority = await query(authority_sql)
        authority = authority[0].grade
        console.log(authority);
        let novels,total;
        if(authority === 'author'){
            let pen_name = await query(`SELECT pen_name FROM user WHERE user_id = ${user_id}`)
            pen_name = pen_name[0].pen_name
            total = await query(`SELECT count(*) as total FROM cwm_novel WHERE novel_author = '${pen_name}'`);
            total = total[0].total
            novels =await query(`SELECT cn.*,ct.type_title FROM cwm_novel cn INNER JOIN cwm_type ct ON cn.type_id = ct.type_id WHERE cn.novel_author ='${pen_name}' limit ${(currPage-1)*pageSize},${pageSize}`)
        }else{
            total = await query(`SELECT count(*) as total FROM cwm_novel `);
            console.log(total);
            total = total[0].total
            novels =await query(`SELECT cn.*,ct.type_title FROM cwm_novel cn INNER JOIN cwm_type ct ON ct.type_id = cn.type_id limit ${(currPage-1)*pageSize},${pageSize}`)
        }
        let result = {total, novels, currPage, pageSize}
        res.send(result)
    })
    // 小说搜索
    router.get('/search_novel', async (req, res)=>{
        const {user_id, currPage, pageSize, keyword} = req.query
        // 查询用户的权限，管理员查看所有，作者只能看自己作品
        let authority_sql = `SELECT grade FROM user WHERE user_id = ${user_id}`
        let authority = await query(authority_sql)
        authority = authority[0].grade
        console.log(keyword);
        let novels,total;
        if(authority === 'author'){
            let pen_name = await query(`SELECT pen_name FROM user WHERE user_id = ${user_id}`)
            pen_name = pen_name[0].pen_name
            total = await query(`SELECT count(*) as total FROM cwm_novel WHERE novel_author = '${pen_name}'
                            AND (novel_title LIKE '%${keyword}%' OR novel_author LIKE '%${keyword}%')`);
            total = total[0].total
            novels =await query(`SELECT cn.*,ct.type_title FROM cwm_novel cn INNER JOIN cwm_type ct ON cn.type_id = ct.type_id
             WHERE cn.novel_author = '${pen_name}'  AND (cn.novel_title LIKE '%${keyword}%' OR cn.novel_author LIKE '%${keyword}%') limit ${(currPage-1)*pageSize},${pageSize}`)
        }else{
            total = await query(`SELECT count(*) as total FROM cwm_novel WHERE novel_title LIKE '%${keyword}%' OR novel_author LIKE '%${keyword}%'`)
            console.log(total);
            total = total[0].total
            novels =await query(`SELECT cn.*,ct.type_title FROM  cwm_novel cn INNER JOIN cwm_type ct ON cn.type_id = ct.type_id 
                WHERE cn.novel_title LIKE '%${keyword}%' OR cn.novel_author LIKE '%${keyword}%' limit ${(currPage-1)*pageSize},${pageSize}`)
        }
        let result = {total, novels, currPage, pageSize}
        res.send(result)
    })
    // 得到小说所有标签
    router.get('/get_novel_types', async (req, res)=>{
        let sql = `SELECT * FROM cwm_type`
        let types = await query(sql)
        res.send(types)
    })
    // 上传小说封面
    const novel_cover_upload = multer({dest: __dirname + '/../../uploads/novels'});
    router.post('/update_novel_cover',novel_cover_upload.single('file'), async (req, res)=>{
        let file = req.file;
        if(req.headers.host === 'localhost:3000'){
            file.url = `http://localhost:3000/uploads/novels/${file.filename}`
         }else{
            file.url = `http://121.196.156.175/uploads/novels/${file.filename}`
         }
        
        res.send(file)
    })
    // 添加小说
    router.post('/insert_novel', async (req, res)=>{
        let {novel_title, novel_author, type_id,novel_tag,
            novel_intro, novel_state,cover} = req.body;
        let sql = `INSERT INTO cwm_novel(novel_title, novel_author, type_id, novel_tag,
            novel_intro, novel_state, cover) values('${novel_title}','${novel_author}',
            ${type_id},'${novel_tag}', '${novel_intro}', '${novel_state}', '${cover}')  ` 
        console.log(sql);
        let row = await query(sql);
        console.log(_.isEmpty(row));
        assert(!_.isEmpty(row), 402, '创建失败') 
        res.send({type:'success',message:"创建成功"})
    })
    // 更新小说
    router.post('/update_novel', async(req, res)=>{
        let {novel_id, novel_title, novel_author, type_id,novel_tag,
            novel_intro, novel_state,cover} = req.body;
        let sql = `UPDATE cwm_novel SET novel_title = '${novel_title}',
        novel_author= '${novel_author}', type_id=${type_id},novel_tag='${novel_tag}',
        novel_intro='${novel_intro}', novel_state = '${novel_state}', 
        cover= '${cover}' WHERE novel_id = ${novel_id}`
        console.log(sql);
        let row = await query(sql);
        assert(!_.isEmpty(row),402,'更新小说失败')
        res.send({type:'success',message:"更新小说成功"})
    })
    // 删除小说
    router.delete('/delete_novel', async (req, res)=>{
        const {novel_id} = req.query
        let sql = `DELETE FROM cwm_novel WHERE novel_id = ${novel_id}`;
        await query(sql)
        res.send({
            type:'success',
            message:'删除成功'
        })
    })

    // 小说卷列表
    router.get('/rolls', async(req, res)=>{
        const {novel_id, pageSize, currPage} = req.query;
        let result,rolls,total
        total = await query(`SELECT count(*) as total FROM cwm_roll WHERE novel_id = ${novel_id}`);
        total = total[0].total
        let sql =`SELECT cn.novel_title,cr.* FROM cwm_novel cn INNER JOIN cwm_roll cr ON  
                    cn.novel_id = cr.novel_id
                    WHERE cn.novel_id = ${novel_id} limit ${(currPage-1)*pageSize},${pageSize} `
        rolls = await query(sql)
        result = {rolls, total, pageSize, currPage}
        res.send(result)
    })

     // 小说卷搜索
     router.get('/search_roll', async (req, res)=>{
        const {novel_id, currPage, pageSize, keyword} = req.query
        let rolls,total;
        total = await query(`SELECT count(*) as total FROM cwm_novel cn INNER JOIN cwm_roll cr ON  
                cn.novel_id = cr.novel_id WHERE cn.novel_id = ${novel_id} AND roll_title LIKE '%${keyword}%'`)
        console.log(total);
        total = total[0].total
        rolls = await query(`SELECT cn.novel_title,cr.* FROM cwm_novel cn INNER JOIN cwm_roll cr ON  
        cn.novel_id = cr.novel_id WHERE cn.novel_id = ${novel_id} AND cr.roll_title LIKE '%${keyword}%' limit ${(currPage-1)*pageSize},${pageSize} `)
        let result = {total, rolls, currPage, pageSize}
        res.send(result)
    })
    // 添加卷
    router.post('/insert_roll', async (req, res)=>{
        const  {novel_id, roll_title} = req.body
        let sql = `INSERT INTO cwm_roll(roll_title, novel_id) values('${roll_title}',${novel_id})  ` 
        console.log(sql);
        let row = await query(sql);
        assert(!_.isEmpty(row), 402, '创建失败') 
        res.send({type:'success',message:"创建成功"})
    })
    // 编辑卷
    router.put('/update_roll', async(req, res)=>{
        const  {roll_id, roll_title} = req.body
        let sql = `UPDATE cwm_roll SET roll_title = '${roll_title}' 
                WHERE  roll_id = ${roll_id} ` 
        console.log(sql);
        let row = await query(sql);
        assert(!_.isEmpty(row), 402, '修改失败') 
        res.send({type:'success',message:"修改成功"})
    })
    // 删除卷
    router.delete('/delete_roll', async (req, res)=>{
        const {roll_id} = req.query
        let sql = `DELETE FROM cwm_roll WHERE roll_id = ${roll_id}`;
        await query(sql)
        res.send({
            type:'success',
            message:'删除成功'
        })
    })
    // 小说章节列表
    router.get('/roll_chapters', async(req, res)=>{
        const { roll_id, pageSize, currPage} = req.query;
        let result,chapters,total
        total = await query(`SELECT count(*) as total FROM cwm_chapter WHERE roll_id = ${roll_id}`);
        total = total[0].total
        let sql =`SELECT cn.novel_title,cr.roll_title,cc.* FROM cwm_novel cn INNER JOIN cwm_roll cr ON  
                    cn.novel_id = cr.novel_id INNER JOIN cwm_chapter cc ON cc.roll_id = cr.roll_id 
                    WHERE cc.roll_id = ${roll_id} limit ${(currPage-1)*pageSize},${pageSize} `
        chapters = await query(sql)
        result = {chapters, total, pageSize, currPage}
        res.send(result)
    })

    //添加小说海报 1. 上传海报图 2. 提交数据插入
     // 1.上传海报图
    const banner_upload = multer({dest: __dirname + '/../../uploads/banners'});
     router.post('/upload_banner',banner_upload.single('file'), async (req, res)=>{
         let file = req.file;
         if(req.headers.host === 'localhost:3000'){
            file.url = `http://localhost:3000/uploads/banners/${file.filename}`
         }else{
            file.url = `http://121.196.156.175/uploads/banners/${file.filename}`
         }
         //file.url = `http://localhost/uploads/banners/${file.filename}`
        //  file.url = `http://121.196.156.175/uploads/banners/${file.filename}`
         res.send(file)
     })
    // 查看小说海报
    router.get('/get_banner', async (req, res)=>{
        const {work_id, table_title} = req.query;
        console.log(work_id, table_title);
        let sql = `SELECT * FROM banners WHERE work_id = ${work_id} AND table_title = '${table_title}'`
        let banners = await query(sql)
        if(banners[0])   banners[0].is_recommend = banners[0].is_recommend?true:false;
        res.send(banners[0])
    })
    // 修改、插入小说海报
    router.put('/update_banner', async(req, res)=>{
        let {work_id, table_title, banner_url,is_recommend, recommend_at} = req.body;
        console.log(work_id, table_title, banner_url,is_recommend, recommend_at);
        assert(!_.isEmpty(banner_url), 402, '上传参数有问题')
        is_recommend = is_recommend?is_recommend:false;
        recommend_at = recommend_at?recommend_at:null;
        if(recommend_at) is_recommend = true; // 设置推荐时间，则默认推荐
        let query_sql = `SELECT * FROM banners WHERE work_id = ${work_id} AND table_title = '${table_title}'`
        console.log(query_sql);
        let banners = await query(query_sql)
        let sql;
        // mysql 数据库中datetime格式只能是null、datetime,不能是空字符串
        if(banners.length>0){
            if(recommend_at){
                sql = `UPDATE banners SET banner_url ='${banner_url}',
                is_recommend = ${is_recommend},recommend_at = '${recommend_at}'
                WHERE work_id = ${work_id} AND table_title = '${table_title}'`
            }else{
                sql = `UPDATE banners SET banner_url ='${banner_url}',
                is_recommend = ${is_recommend},recommend_at = ${recommend_at}
                WHERE work_id = ${work_id} AND table_title = '${table_title}'`
            }     
        }else{
            if(recommend_at){
                sql = `INSERT INTO banners(work_id,table_title,banner_url,is_recommend,recommend_at)
                values(${work_id}, '${table_title}', '${banner_url}', ${is_recommend}, '${recommend_at}')`
            }else{
                sql = `INSERT INTO banners(work_id,table_title,banner_url,is_recommend,recommend_at)
                values(${work_id}, '${table_title}', '${banner_url}', ${is_recommend}, ${recommend_at})`
            }
        }
        console.log(sql);
        await query(sql)
        res.send({type:'success',message:'操作成功'})
    })


     // 小说章节搜索
     router.get('/search_chapter', async (req, res)=>{
        const {roll_id, currPage, pageSize, keyword} = req.query
        let chapters,total;
        total = await query(`SELECT count(*) as total FROM cwm_chapter WHERE roll_id = ${roll_id} AND chap_title LIKE '%${keyword}%'`)
        console.log(total);
        total = total[0].total
        chapters = await query(`SELECT cn.novel_title,cr.roll_title,cc.* FROM cwm_novel cn INNER JOIN cwm_roll cr ON  
        cn.novel_id = cr.novel_id INNER JOIN cwm_chapter cc ON cc.roll_id = cr.roll_id 
        WHERE cc.roll_id = ${roll_id}  AND cc.chap_title LIKE '%${keyword}%' limit ${(currPage-1)*pageSize},${pageSize} `)
        let result = {total, chapters, currPage, pageSize}
        res.send(result)
    })
    // 添加章节 
    // todo 考虑到实际内容编辑时间才是该章节发布的时间。
    router.post('/insert_chapter', async (req, res)=>{
        const  {roll_id, chap_title} = req.body
        let ctime = dayjs(Date.now()).format('YYYY-MM-DD HH:mm:ss')
        let sql = `INSERT INTO cwm_chapter(chap_title, roll_id, ctime) values('${chap_title}',${roll_id},'${ctime}') ` 
        console.log(sql);
        let row = await query(sql);
        assert(!_.isEmpty(row), 402, '创建失败') 
        res.send({type:'success',message:"创建成功"})
    })
    // 编辑章节
    router.put('/update_chapter', async(req, res)=>{
        const  {chap_id, chap_title} = req.body
        let sql = `UPDATE cwm_chapter SET chap_title = '${chap_title}' 
                WHERE  chap_id = ${chap_id} ` 
        console.log(sql);
        let row = await query(sql);
        assert(!_.isEmpty(row), 402, '修改失败') 
        res.send({type:'success',message:"修改成功"})
    })
    // 删除章节
    router.delete('/delete_chapter', async (req, res)=>{
        const {chap_id} = req.query
        let sql = `DELETE FROM cwm_chapter WHERE chap_id = ${chap_id}`;
        await query(sql)
        res.send({
            type:'success',
            message:'删除成功'
        })
    })
    // 查询章节下的内容
    router.get('/chap_content', async (req, res)=>{
        const {chap_id} = req.query;
        let sql = `SELECT * FROM cwm_content WHERE chap_id = ${chap_id}`;
        let content = await query(sql)
        console.log(content);
        res.send(content[0])
    })
    // 插入内容
    router.post('/insert_content', async (req, res)=>{
        const {chap_id, content_text} = req.body;
        let sql = `INSERT INTO cwm_content(content_text, chap_id) values('${content_text}',${chap_id})`
        await query(sql)
        res.send({type:'success',message:'插入成功'})
    })
    // 更新内容
    router.put('/update_content', async (req, res)=>{
        const {content_id , content_text} = req.body;
        console.log(content_id);
        let sql = `UPDATE cwm_content SET content_text='${content_text}'  WHERE content_id = ${content_id}`;
        await query(sql)
        res.send({type:'success',message:'更新成功'})
    })

    /***
     *  漫画操作
     */
    // 漫画列表
    router.get('/comics', async (req, res)=>{
        const {user_id, currPage, pageSize} = req.query
        // 查询用户的权限，管理员查看所有，作者只能看自己作品
        let authority_sql = `SELECT grade FROM user WHERE user_id = ${user_id}`
        let authority = await query(authority_sql)
        authority = authority[0].grade
        console.log(authority);
        let comics,total;
        if(authority === 'author'){
            let pen_name = await query(`SELECT pen_name FROM user WHERE user_id = ${user_id}`)
            pen_name = pen_name[0].pen_name
            total = await query(`SELECT count(*) as total FROM comic_book WHERE comic_author = '${pen_name}'`);
            total = total[0].total
            comics =await query(`SELECT * FROM comic_book WHERE comic_author ='${pen_name}' limit ${(currPage-1)*pageSize},${pageSize}`)
        }else{
            total = await query(`SELECT count(*) as total FROM comic_book `);
            console.log(total);
            total = total[0].total
            comics =await query(`SELECT * FROM comic_book limit ${(currPage-1)*pageSize},${pageSize}`)
        }
        console.log(comics);
        let result = {total, comics, currPage, pageSize}
        res.send(result)
    })
    // 删除漫画
    router.delete('/delete_comic', async(req, res)=>{
        const {comic_id} = req.query
        let sql = `DELETE FROM comic_book WHERE comic_id = ${comic_id}`;
        await query(sql)
        res.send({
            type:'success',
            message:'删除成功'
        })
    })
    // 搜索漫画
    router.get('/search_comic', async (req, res)=>{
        const {user_id, currPage, pageSize, keyword} = req.query
        // 查询用户的权限，管理员查看所有，作者只能看自己作品
        let authority_sql = `SELECT grade FROM user WHERE user_id = ${user_id}`
        let authority = await query(authority_sql)
        authority = authority[0].grade
        console.log(user_id, currPage, pageSize,keyword, authority);
        let comics,total;
        if(authority === 'author'){
            let pen_name = await query(`SELECT pen_name FROM user WHERE user_id = ${user_id}`)
            
            pen_name = pen_name[0].pen_name
            total = await query(`SELECT count(*) as total FROM comic_book WHERE comic_author = '${pen_name}'
                            AND (comic_title LIKE '%${keyword}%' OR comic_author LIKE '%${keyword}%')`);
            total = total[0].total
            comics =await query(`SELECT* FROM comic_book  WHERE comic_author = '${pen_name}'  AND (comic_title LIKE '%${keyword}%' OR comic_author LIKE '%${keyword}%') limit ${(currPage-1)*pageSize},${pageSize}`)
            console.log(comics)
        }else{
            total = await query(`SELECT count(*) as total FROM comic_book WHERE comic_title LIKE '%${keyword}%' OR comic_author LIKE '%${keyword}%'`)
            console.log(total);
            total = total[0].total
            comics =await query(`SELECT * FROM comic_book  WHERE comic_title LIKE '%${keyword}%' OR comic_author LIKE '%${keyword}%' limit ${(currPage-1)*pageSize},${pageSize}`)
        }
        let result = {total, comics, currPage, pageSize}
        res.send(result)
    })

    // 上传漫画封面
    const comic_cover_upload = multer({dest: __dirname + '/../../uploads/comics'});
    router.post('/update_comic_cover',comic_cover_upload.single('file'), async (req, res)=>{
        let file = req.file;
        // file.url = `http://localhost:3000/uploads/comics/${file.filename}`
        if(req.headers.host === 'localhost:3000'){
            file.url = `http://localhost:3000/uploads/comics/${file.filename}`
         }else{
            file.url = `http://121.196.156.175/uploads/comics/${file.filename}`
         }
        // file.url = `http://121.196.156.175/uploads/comics/${file.filename}`
        res.send(file)
    })

       // 插入漫画
       router.post('/insert_comic', async (req, res)=>{
        let {comic_title, comic_author, comic_type,
            comic_intro, comic_cover,comic_state} = req.body;
        let sql = `INSERT INTO comic_book(comic_title, comic_author, comic_type, comic_intro,
            comic_cover, comic_state) values('${comic_title}','${comic_author}',
            '${comic_type}','${comic_intro}', '${comic_cover}', '${comic_state}')  ` 
        console.log(sql);
        let row = await query(sql);
        console.log(_.isEmpty(row));
        assert(!_.isEmpty(row), 402, '创建失败') 
        res.send({type:'success',message:'创建成功'})
    })

    // 更新漫画
    router.post('/update_comic', async(req, res)=>{
        let {comic_id, comic_title, comic_author, comic_type,
            comic_intro, comic_cover,comic_state} = req.body;
        let sql = `UPDATE comic_book SET comic_title = '${comic_title}',
        comic_author= '${comic_author}', comic_type='${comic_type}',
        comic_intro='${comic_intro}',comic_cover='${comic_cover}', 
        comic_state = '${comic_state}' WHERE comic_id = ${comic_id}`
        console.log(sql);
        let row = await query(sql);
        assert(!_.isEmpty(row),402,'更新漫画失败')
        res.send({type:'success',message:"更新漫画成功"})
    })
    // 显示漫画章节列表
    router.get('/comic_chapters', async(req, res)=>{
        const {comic_id, pageSize, currPage} = req.query;
        let result,chapters,total
        total = await query(`SELECT count(*) as total FROM comic_chapter WHERE comic_id = ${comic_id}`);
        total = total[0].total
        let sql =`SELECT cb.comic_title, cc.* FROM comic_book cb INNER JOIN comic_chapter cc ON  
                    cb.comic_id = cc.comic_id
                    WHERE cc.comic_id = ${comic_id} limit ${(currPage-1)*pageSize},${pageSize} `
                    chapters = await query(sql)
        result = {chapters, total, pageSize, currPage}
        res.send(result)
    })

// 漫画章节搜索
router.get('/search_comic_chapter', async (req, res)=>{
    const {comic_id, currPage, pageSize, keyword} = req.query
    let chapters,total;
    total = await query(`SELECT count(*) as total FROM comic_chapter WHERE comic_id = (SELECT comic_id FROM comic_book WHERE comic_id = ${comic_id}) 
        AND comic_chap_title LIKE '%${keyword}%'`)
    console.log(total);
    total = total[0].total
    chapters = await query(`SELECT cb.comic_title, cc.* FROM comic_book cb INNER JOIN comic_chapter cc ON  
    cb.comic_id = cc.comic_id WHERE cc.comic_id = ${comic_id} AND cc.comic_chap_title LIKE '%${keyword}%' limit ${(currPage-1)*pageSize},${pageSize} `)
    let result = {total, chapters, currPage, pageSize}
    res.send(result)
})
// 添加漫画章节
router.post('/insert_comic_chapter', async (req, res)=>{
    const  {comic_id, comic_chap_title} = req.body
    let sql = `INSERT INTO comic_chapter(comic_chap_title, comic_id) values('${comic_chap_title}',${comic_id})  ` 
    console.log(sql);
    let row = await query(sql);
    assert(!_.isEmpty(row), 402, '创建失败') 
    res.send({type:'success',message:"创建成功"})
})
// 编辑漫画章节
router.put('/update_comic_chapter', async(req, res)=>{
    const  {comic_chap_id, comic_chap_title} = req.body
    let sql = `UPDATE comic_chapter SET comic_chap_title = '${comic_chap_title}' 
            WHERE  comic_chap_id = ${comic_chap_id} ` 
    console.log(sql);
    let row = await query(sql);
    assert(!_.isEmpty(row), 402, '修改失败') 
    res.send({type:'success',message:"修改成功"})
})
// 删除漫画章节
router.delete('/delete_comic_chapter', async (req, res)=>{
    const {comic_chap_id} = req.query
    let sql = `DELETE FROM comic_chapter WHERE comic_chap_id = ${comic_chap_id}`;
    await query(sql)
    res.send({
        type:'success',
        message:'删除成功'
    })
})
// 显示漫画内容
router.get('/comic_contents', async(req, res)=>{
    const {comic_chap_id} = req.query;
    let sql = `SELECT * FROM comic_content WHERE comic_chap_id = ${comic_chap_id}`;
    let contents = await query(sql)
    res.send(contents)
})
// upload_comic_uri

// 插入漫画
router.put('/insert_comic_contents', async (req, res)=>{
    const {comic_chap_id, contents} = req.body;
    let del_sql = `DELETE FROM comic_content WHERE comic_chap_id = ${comic_chap_id}`
    console.log(del_sql);
    await query(del_sql)
    assert(!_.isEmpty(contents), 402, "内容已经为空")
    let insert_sql = `INSERT INTO comic_content(comic_con_src, comic_con_page, comic_chap_id) values`
    contents.forEach((item,index)=>{
        insert_sql += `('${item.url}','${index+1}',${comic_chap_id})`
        if(index !== contents.length-1){
            insert_sql+=`,`
        }
    })
    console.log(insert_sql);
    await query(insert_sql)
    res.send({type:'success',message:'操作成功'})

})

// 修改漫画

    /***
     *  电子书操作
     */
    router.get('/get_ebooks', async (req, res)=>{
        const {user_id, currPage, pageSize} = req.query
        // 查询用户的权限，管理员查看所有，作者只能看自己作品
        let authority_sql = `SELECT grade FROM user WHERE user_id = ${user_id}`
        let authority = await query(authority_sql)
        authority = authority[0].grade
        console.log(authority);
        let ebooks,total;
        if(authority === 'author'){
            let pen_name = await query(`SELECT pen_name FROM user WHERE user_id = ${user_id}`)
            pen_name = pen_name[0].pen_name
            total = await query(`SELECT count(*) as total FROM ebook WHERE ebook_author = '${pen_name}'`);
            total = total[0].total
            ebooks =await query(`SELECT e.*,et.type_title FROM ebook e INNER JOIN ebook_type et ON e.type_id = et.type_id WHERE e.ebook_author = '${pen_name}' limit ${(currPage-1)*pageSize},${pageSize}`)
        }else{
            total = await query(`SELECT count(*) as total FROM ebook `);
            console.log(total);
            total = total[0].total
            ebooks =await query(`SELECT e.*,et.type_title FROM ebook e INNER JOIN ebook_type et ON e.type_id = et.type_id limit ${(currPage-1)*pageSize},${pageSize}`)
        }
        let result = {total, ebooks, currPage, pageSize}
        res.send(result)
    })

    //搜索电子书
    router.get('/search_ebooks', async (req, res)=>{
        const {user_id, currPage, pageSize, keyword} = req.query
        // 查询用户的权限，管理员查看所有，作者只能看自己作品
        let authority_sql = `SELECT grade FROM user WHERE user_id = ${user_id}`
        let authority = await query(authority_sql)
        authority = authority[0].grade
        console.log(keyword);
        let ebooks,total;
        if(authority === 'author'){
            let pen_name = await query(`SELECT pen_name FROM user WHERE user_id = ${user_id}`)
            pen_name = pen_name[0].pen_name
            total = await query(`SELECT count(*) as total FROM ebook WHERE ebook_author = '${pen_name}'
                            AND (ebook_title LIKE '%${keyword}%' OR ebook_author LIKE '%${keyword}%')`);
            total = total[0].total
            ebooks =await query(`SELECT e.*,et.type_title FROM ebook e INNER JOIN ebook_type et ON e.type_id = et.type_id
             WHERE e.ebook_author = '${pen_name}'  AND (ebook_title LIKE '%${keyword}%' OR ebook_author LIKE '%${keyword}%') limit ${(currPage-1)*pageSize},${pageSize}`)
        }else{
            total = await query(`SELECT count(*) as total FROM ebook WHERE ebook_title LIKE '%${keyword}%' OR ebook_author LIKE '%${keyword}%'`)
            console.log(total);
            total = total[0].total
            ebooks =await query(`SELECT e.*,et.type_title FROM ebook e INNER JOIN ebook_type et ON e.type_id = et.type_id 
                WHERE e.ebook_title LIKE '%${keyword}%' OR e.ebook_author LIKE '%${keyword}%' limit ${(currPage-1)*pageSize},${pageSize}`)
        }
        let result = {total, ebooks, currPage, pageSize}
        res.send(result)
    })

    //得到电子书的类别
    router.get('/get_ebook_types', async (req, res)=>{
        let sql = `SELECT * FROM ebook_type`
        let types = await query(sql)
        res.send(types)
    })
    // 上传电子书的封面 
    const epub_cover_upload = multer({dest: __dirname + '/../../uploads/epubs'});
    router.post('/update_ebook_cover',epub_cover_upload.single('file'), async (req, res)=>{
        let file = req.file;
        if(req.headers.host === 'localhost:3000'){
            file.url = `http://localhost:3000/uploads/epubs/${file.filename}`
         }else{
            file.url = `http://121.196.156.175/uploads/epubs/${file.filename}`
         }
        res.send(file)
    })

    // 上传电子书
    var storage = multer.diskStorage({
        destination: function (req, file, cb) {
          cb(null,  __dirname + '/../../uploads/epubs')
        },
        filename: function (req, file, cb) {
          cb(null, file.originalname)
        }
      })
    const epub_upload = multer({ storage: storage });
    router.post('/upload_ebook_uri',epub_upload.single('file'), async (req, res)=>{
        let file = req.file;
        const {ebook_id} = req.body;
        // file.url = `http://localhost:3000/uploads/epubs/${file.originalname}`
        // 换成线上地址
        if(req.headers.host === 'localhost:3000'){
            file.url = `http://localhost:3000/uploads/epubs/${file.originalname}`
         }else{
            file.url = `http://121.196.156.175/uploads/epubs/${file.originalname}`
         }
        // file.url = `http://121.196.156.175/uploads/epubs/${file.originalname}`
        let sql = `UPDATE ebook SET ebook_url = '${file.url}' WHERE ebook_id = ${ebook_id}`;
        // console.log(sql);
        query(sql)
        res.send(file)
    })
    // 更新电子书
    router.post('/upload_ebook', async(req, res)=>{
        // console.log(req.body);
        let {ebook_id, ebook_title, ebook_author, type_id,
             ebook_tag, ebook_eintro,ebook_aintro, ebook_ctime} = req.body.ebook;
        ebook_ctime = dayjs(ebook_ctime).format('YYYY-MM-DD HH:mm:ss')
        // console.log(ebook_ctime);
        let sql = `UPDATE ebook SET ebook_title = '${ebook_title}',
                ebook_author= '${ebook_author}', type_id=${type_id},ebook_tag='${ebook_tag}',
                ebook_eintro='${ebook_eintro}', ebook_aintro = '${ebook_aintro}', 
                ebook_ctime= '${ebook_ctime}' WHERE ebook_id = ${ebook_id}`
        // console.log(sql);
        let row = await query(sql);
        res.send(row)
    })

      // 插入电子书
    router.post('/insert_ebook', async (req, res)=>{
        let {ebook_title, ebook_author, type_id,ebook_ISBN,
            ebook_tag, ebook_eintro,ebook_aintro,ebook_cover, ebook_ctime} = req.body.ebook;
        ebook_ctime = dayjs(ebook_ctime).format('YYYY-MM-DD HH:mm:ss')
        let sql = `INSERT INTO ebook(ebook_title, ebook_author, type_id, ebook_tag,
            ebook_eintro, ebook_aintro, ebook_ctime, ebook_ISBN, ebook_cover) values('${ebook_title}','${ebook_author}',
            ${type_id},'${ebook_tag}', '${ebook_eintro}', '${ebook_aintro}', '${ebook_ctime}', '${ebook_ISBN}', '${ebook_cover}')` 
        console.log(sql);
        let row = await query(sql);
        console.log(_.isEmpty(row));
        assert(!_.isEmpty(row), 402, '创建失败') 
        let ebook_id = await query('SELECT ebook_id FROM ebook order by ebook_id DESC limit 1');
        res.send(ebook_id)
    })
    // 删除ebook
    router.delete('/delete_ebook', async (req, res)=>{
        const {ebook_id} = req.query
        let sql = `DELETE FROM ebook WHERE ebook_id = ${ebook_id}`;
        await query(sql)
        res.send(ebook_id)
    })

    // 审核作者列表
    router.get('/get_review_authors', async(req, res)=>{
        const {review_result, pageSize, currPage} = req.query
        let total,result, reviews ;
        total = await query(`SELECT count(*) as total FROM review_author WHERE review_result = ${review_result}`)
        total = total[0].total
        let sql = `SELECT * FROM review_author WHERE review_result = ${review_result} ORDER BY created_at DESC
            LIMIT ${(currPage-1)*pageSize},${pageSize}`
        reviews =  await query(sql)
        result ={total,reviews,currPage, pageSize}
        res.send(result)
    })
    // 审核作者
    router.put('/review_author', async (req, res)=>{
        let {review_id, user_id, user_desc, user_name, user_email, user_pwd, pen_name, feedback} = req.body
        let user_sql,review_sql, result;
        user_pwd = md5(user_pwd)
        let review_at = dayjs(Date.now()).format('YYYY-MM-DD HH:mm:ss')
        console.log(feedback);
        // 审核通过
        if(!feedback){
            feedback = '恭喜您，审核通过了';
            user_sql = `UPDATE user SET user_name='${user_name}',user_desc='${user_desc}', user_email='${user_email}',
                 user_pwd='${user_pwd}', pen_name='${pen_name}', grade='author' WHERE user_id = ${user_id}`;    
            await query(user_sql)
            review_sql = `UPDATE review_author SET review_result = 1 , review_at = '${review_at}', feedback='${feedback}' WHERE review_id = ${review_id}`;
            result = {type:'success',message:'审核通过'}
        }else{
            review_sql =  `UPDATE review_author SET review_result = -1 , review_at = '${review_at}', feedback='${feedback}' WHERE review_id = ${review_id}`;
            result = {type:'error',message:'审核未通过'}
        }
        await query(review_sql)
        res.send(result)
    })

    // 子路由请求，添加验证权限中间键
    app.use('/admin/api',router)


    // 全局路由请求
    app.post('/admin/api/login',async (req, res)=>{
        const { account, password } = req.body
        
        // 1. 根据用户名查找密码
        const user = await query('select * from user where user_account =?',[account])
        console.log(user);
        assert(!_.isEmpty(user), 422, '用户不存在')
        // 2. 判断是否是普通用户
        assert(user[0].grade !== 'user', 422, '普通用户无权登录')
        // 3. 检验密码
        assert(password === user[0].user_pwd, 422, '密码错误')
        // 4. 返回token
        const token = jwt.sign({account:account}, app.get('secret'))
        res.send({token:token, id:user[0].user_id})

    })
    app.post('/admin/api/register',async (req, res)=>{
        const {account, password} = req.body;
        sql = 'select * from user where user_account = ?';
        user = await query(sql, [account])
        assert(_.isEmpty(user[0]), 422, '注册失败，该账户已经存在')
        let ctime = dayjs(Date.now()).format('YYYY-MM-DD HH:mm:ss')
        row = await query('insert into user(user_account, user_pwd, ctime) values(?,?,?)',[account, password, ctime])
        res.status(200).send({message:'注册成功',type:'success'});
    })

    
    const upload = multer({dest: __dirname + '/../../uploads'});
    app.post('/admin/api/upload', upload.single("file"), async(req, res)=>{
        let file = req.file;
        if(req.headers.host === 'localhost:3000'){
            file.url = `http://localhost:3000/uploads/${file.filename}`
         }else{
            file.url = `http://121.196.156.175/uploads/${file.filename}`
         }
        // file.url = `http://localhost:3000/uploads/${file.filename}`
        const {user_id} = req.body;
        console.log(user_id);
        if(user_id){
            let sql = 'UPDATE user SET user_cover= ? WHERE user_id = ?'
            query(sql, [file.url, user_id])
        }
        res.send(file)
    })

    
    // 错误处理函数
    app.use(async (err, req, res, next) => {
        console.log(err.statusCode)
        res.status(err.statusCode || 500).send({
            message: err.message
        })
    })
}